package com.milk.Dao;

import com.milk.DB.DBConnection;
import com.milk.entity.Stock;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StockDao {
    // 查询所有库存信息
    public static List<Stock> findAll() {
        List<Stock> stocks = new ArrayList<>();
        String sql = "SELECT * FROM stock";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                Stock stock = new Stock();
                stock.setDrinkId(resultSet.getInt("drink_id"));
                stock.setQuantity(resultSet.getInt("quantity"));
                stock.setLowStockThreshold(resultSet.getInt("low_stock_threshold"));
                stocks.add(stock);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stocks;
    }

    // 根据饮品ID查询库存信息
    public static Stock findByDrinkId(int drinkId) {
        Stock stock = null;
        String sql = "SELECT * FROM stock WHERE drink_id =?";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, drinkId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                stock = new Stock();
                stock.setDrinkId(resultSet.getInt("drink_id"));
                stock.setQuantity(resultSet.getInt("quantity"));
                stock.setLowStockThreshold(resultSet.getInt("low_stock_threshold"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stock;
    }

    public static int updateQuantity(int drinkId, int newQuantity) {
        String sql = "UPDATE stock SET quantity = ? WHERE drink_id = ?";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, newQuantity);
            statement.setInt(2, drinkId);
            return statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    // 获取库存预警信息（返回库存数量低于预警阈值的库存记录列表）
    public static List<Stock> getLowStockAlert() {
        List<Stock> lowStockList = new ArrayList<>();
        String sql = "SELECT * FROM stock WHERE quantity < low_stock_threshold";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                Stock stock = new Stock();
                stock.setDrinkId(resultSet.getInt("drink_id"));
                stock.setQuantity(resultSet.getInt("quantity"));
                stock.setLowStockThreshold(resultSet.getInt("low_stock_threshold"));
                lowStockList.add(stock);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lowStockList;
    }
}